package com.mmy.dao.train;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.mmy.domain.train.TTrainOrder;

public interface TrainOrderRepository extends JpaRepository<TTrainOrder, Integer> {

	/**
	 * 获取最大流水号
	 * 
	 * @return 返回值说明 流水号
	 * @author 创建人 hanxl
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "SELECT MAX(iID)  FROM tTrainOrder", nativeQuery = true)
	Object getMaxID();

	/**
	 * 获得订单最大编码
	 *
	 * @author 创建人dingxd
	 * @date 修改日期 2020.11.17
	 */
	@Query(value = "SELECT MAX(cOrderID) FROM tTrainOrder WHERE LEFT(cOrderID,16) = CONCAT(:iPlanID,REPLACE(DATE(NOW()),'-',''))", nativeQuery = true)
	Object getMaxCode(@Param("iPlanID") String strPlanID);

	/**
	 * 创建
	 * 
	 * @return
	 * @author 创建人 hanxl
	 * @date 创建日期 2020.10.29
	 */
	@Transactional
	@Modifying
	@Query(value = "INSERT INTO tTrainOrder(iID, cOrderID, iReserveUserID, dReserveDate, cReserveName, cReserveTel, iOrderUserID, dOrderDate, cOrderName, cOrderTel, iOrderState, fOrderAmount, fReceivable, fReceipt, fFloor, fPeer, fPrice, iPayType, cVoucher, iCashierState, dCashierDate, iCashierID, iAccountState, dAccountDate, iAccountID, iInvoiceState, iRefundState, iRefundProcess, bRemittanceState, dCreateDate, cCreator, dModifyDate, cReviser, cMemo, cSpare1, cSpare2, cSpare3, cSpare4, cSpare5) VALUES (:id, NULL, 0, NULL, NULL, NULL, 0, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, 0, NULL, 0, 0, NULL, 0, 0, 0, 0, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)", nativeQuery = true)
	void save(@Param("id") int id);

	/**
	 * 根据订单ID查询
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select *  from tTrainOrder where iID = :iID", nativeQuery = true)
	TTrainOrder findByID(@Param("iID") int iID);

	/*
	 * 根据id删除
	 */
	@Transactional
	@Modifying
	@Query(value = "delete from tTrainOrder where tTrainOrder.iID in (:lstID)", nativeQuery = true)
	void delTrainOrder(@Param("lstID") List<Integer> lstID);

	/**
	 * 个人中心通过登录人获取列车票预留
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select *  from tTrainOrder where iReserveUserID=?1 and iOrderState=0 order by dReserveDate desc limit ?2,?3", nativeQuery = true)
	List<TTrainOrder> findByUserID(int iUserID, int iTempPageNo, int iPageSize);

	/**
	 * 个人中心通过登录人获取列车票订单
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select *  from tTrainOrder where iOrderUserID=?1 and iOrderState in(1,2) order by dOrderDate desc limit ?2,?3", nativeQuery = true)
	List<TTrainOrder> findByOrderUserID(int iUserID, int iTempPageNo, int iPageSize);

	/**
	 * 个人中心通过登录人获取列车票退单
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select *  from tTrainOrder where iOrderUserID=?1 and iOrderState =4 order by dOrderDate desc limit ?2,?3", nativeQuery = true)
	List<TTrainOrder> findByChargeBackUserID(int iUserID, int iTempPageNo, int iPageSize);

	/**
	 * 个人中心通过登录人获取专列预留 获取分页
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select count(*)  from tTrainOrder where iReserveUserID=?1 and iOrderState=0 order by dReserveDate desc", nativeQuery = true)
	int countByUserID(int iUserID);

	/**
	 * 个人中心通过登录人获取专列退单 获取分页
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select count(*)  from tTrainOrder where iOrderUserID=?1 and iOrderState =4 order by dOrderDate desc", nativeQuery = true)
	int countByRefundUserID(int iUserID);

	/**
	 * 个人中心通过登录人获取专列订单 获取分页
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 */
	@Query(value = "select count(*)  from tTrainOrder where iOrderUserID=?1 and iOrderState in(1,2) order by dOrderDate desc", nativeQuery = true)
	int countByOrderUserID(int iUserID);

}
